﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Web;

namespace StockPoint.Util
{
    public class DBUtil
    {
        private SqlConnection cSqlConn;
        private SqlCommand cSqlCommand;

        private string sConnStr;

        /// <summary>
        /// Initialize a new instance of the BNS.Util.DB.ConnectionString with connection data and error handling type.
        /// </summary>
        /// <param name="ConnStr">The ConnStr that indicates database connection information.</param>
        public DBUtil(string ConnStr)
        {
            this.sConnStr = ConnStr;
            this.cSqlConn = new SqlConnection(sConnStr);
        }

        /// <summary>
        /// Throw ApplicationException with Querystring when occured error.
        /// </summary>
        /// <param name="ex">Exception</param>
        /// <param name="sQuery">Querystring</param>
        /// <returns>ApplicationException</returns>
        private ApplicationException NewCustomException(Exception ex, string sQuery)
        {
            ApplicationException cAppEx = new ApplicationException(ex.Message, ex);
            // With...
            return cAppEx;
        }

        #region " Public Method "

        /// <summary>
        /// Determines whether the dabase is available.
        /// </summary>
        /// <returns>If database is avilable then return ture, else return false.</returns>
        /// <example>
        /// <code>
        /// private void Example() {
        ///     DBUtil cDBUtil = new DBUtil("server=Example;uid=test;pwd=test;database=Example");
        ///     string sResult;
        /// 
        ///     if ((cDBUtil.CheckDB() == true))
        ///         sResult = "Database is available";
        ///     else
        ///         sResult = "Database is not available";
        /// }
        /// </code>
        /// </example>
        public bool CheckDB()
        {
            try
            {
                if (this.sConnStr == string.Empty)
                {
                    return false;
                }

                SqlConnection SqlConn = new SqlConnection(this.sConnStr);
                SqlConn.Open();
                SqlConn.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// Executes query.
        /// </summary>
        /// <param name="sQuery">Querystring</param>
        /// <returns>Return affected row count.</returns>
        /// <example>
        /// <code>
        /// private void Example() {
        ///     DBUtil cDBUtil = new DBUtil("server=Example;uid=test;pwd=test;database=Example");
        ///     string sSql;
        ///     int iAffectedRow;
        /// 
        ///     sSql = "UPDATE TEST SET A=1";
        ///     iAffectedRow = cDBUtil.ExecQuery(sSql);
        ///     //cDBUtil.ExecQuery(sSql);
        /// }
        /// </code>
        /// </example>
        public int ExecQuery(string sQuery)
        {
            int iAffectedCount = 0;

            try
            {
                this.cSqlConn.Open();
                this.cSqlCommand = new SqlCommand(sQuery, this.cSqlConn);
                iAffectedCount = this.cSqlCommand.ExecuteNonQuery();
                return iAffectedCount;
            }
            catch (Exception ex)
            {
                throw NewCustomException(ex, sQuery);
            }
            finally
            {
                this.cSqlConn.Close();
            }
        }

        /// <summary>
        /// Gets single value for Integer.
        /// </summary>
        /// <param name="sQuery">Querystring</param>
        /// <returns>Return value.</returns>
        /// <example>
        /// <code>
        /// private void Example() {
        ///     DBUtil cDBUtil = new DBUtil("server=Example;uid=test;pwd=test;database=Example");
        ///     string sSql;
        ///     int iRowCount;
        ///     
        ///     sSql = "SELECT COUNT(*) FROM Example";
        ///     iRowCount = cDBUtil.GetSingleInt(sSql);
        /// }
        /// </code>
        /// </example>
        public int GetSingleInt(string sQuery)
        {
            int iValue = 0;

            try
            {
                this.cSqlConn.Open();
                this.cSqlCommand = new SqlCommand(sQuery, this.cSqlConn);
                iValue = Convert.ToInt32(this.cSqlCommand.ExecuteScalar());
                return iValue;
            }
            catch (Exception ex)
            {
                throw NewCustomException(ex, sQuery);
            }
            finally
            {
                this.cSqlConn.Close();
            }
        }

        /// <summary>
        /// Gets single value for String.
        /// </summary>
        /// <param name="sQuery">Querystring</param>
        /// <returns>Return value.</returns>
        /// <example>
        /// <code>
        /// private void Example() {
        ///     DBUtil cDBUtil = new DBUtil("server=Example;uid=test;pwd=test;database=Example");
        ///     string sSql;
        ///     string sName;
        /// 
        ///     sSql = "SELECT Name FROM Example";
        ///     sName = cDBUtil.GetSingleStr(sSql);
        /// }
        /// </code>
        /// </example>
        public string GetSingleStr(string sQuery)
        {
            string sValue = String.Empty;

            try
            {
                this.cSqlConn.Open();
                this.cSqlCommand = new SqlCommand(sQuery, this.cSqlConn);
                object value = this.cSqlCommand.ExecuteScalar();
                sValue = value == null ? string.Empty : value.ToString();
                return sValue;
            }
            catch (Exception ex)
            {
                throw NewCustomException(ex, sQuery);
            }
            finally
            {
                this.cSqlConn.Close();
            }
        }

        /// <summary>
        /// Gets single value for DateTime.
        /// </summary>
        /// <param name="sQuery">Querystring</param>
        /// <returns>Return value.</returns>
        /// <example>
        /// <code>
        /// private void Example() {
        ///     DBUtil cDBUtil = new DBUtil("server=Example;uid=test;pwd=test;database=Example");
        ///     string sSql;
        ///     DateTime sDT;
        /// 
        ///     sSql = "SELECT RegisterDateTime FROM Example";
        ///     sDT = cDBUtil.GetSingleStr(sSql);
        /// }
        /// </code>
        /// </example>
        public DateTime GetSingleDT(string sQuery)
        {
            DateTime dtValue = DateTime.MinValue;

            try
            {
                this.cSqlConn.Open();
                this.cSqlCommand = new SqlCommand(sQuery, this.cSqlConn);
                dtValue = Convert.ToDateTime(this.cSqlCommand.ExecuteScalar());
                return dtValue;
            }
            catch (Exception ex)
            {
                throw NewCustomException(ex, sQuery);
            }
            finally
            {
                this.cSqlConn.Close();
            }
        }

        /// <summary>
        /// Gets 1 row data for DataRow.
        /// </summary>
        /// <param name="sQuery">Querystring</param>
        /// <returns>DataRow.</returns>
        /// <example>
        /// <code>
        /// private void Example() {
        ///     DBUtil cDBUtil = new DBUtil("server=Example;uid=test;pwd=test;database=Example");
        ///     string sSql;
        ///     DataRow cDataRow;
        /// 
        ///     sSql = "SELECT Column1, Column2, Column3 FROM Example";
        ///     cDataRow = cDBUtil.GetDataRow(sSql);
        /// 
        ///     string sColumn1;
        ///     int sColumn2;
        ///     string sColumn3;
        /// 
        ///     sColumn1 = DBUtil.GetValueStr(cDataRow["Column1"])
        ///     iColumn2 = DBUtil.GetValueInt(cDataRow["Column2"])
        ///     sColumn3 = DBUtil.GetValueStr(cDataRow["Column3"], "AA")
        /// }
        /// </code>
        /// </example>
        public DataRow GetDataRow(string sQuery)
        {
            DataTable cDataTable = new DataTable();

            try
            {
                this.cSqlConn.Open();
                SqlDataAdapter cSDAdapter = new SqlDataAdapter(sQuery, this.cSqlConn);
                cSDAdapter.Fill(cDataTable);

                if ((cDataTable.Rows.Count > 0))
                    return cDataTable.Rows[0];
                else
                    return null;
            }
            catch (Exception ex)
            {
                throw NewCustomException(ex, sQuery);
            }
            finally
            {
                this.cSqlConn.Close();
            }
        }

        /// <summary>
        /// Gets data for DataTable
        /// </summary>
        /// <param name="sQuery">Querystring</param>
        /// <returns>DataTable</returns>
        /// <example>
        /// <code>
        /// private void Example() {
        ///     DBUtil cDBUtil = new DBUtil("server=Example;uid=test;pwd=test;database=Example");
        ///     string sSql;
        ///     DataTable cDataTable;
        /// 
        ///     sSql = "SELECT Column1, Column2, Column3 FROM Example";
        ///     cDataTable = cDBUtil.GetDataTable(sSql);
        /// 
        ///     string sColumn1;
        ///     int sColumn2;
        ///     string sColumn3;
        /// 
        ///     foreach (DataRow cRow in cDataTable.Rows)
        ///     {
        ///         sColumn1 = DBUtil.GetValueStr(cRow["Column1"])
        ///         iColumn2 = DBUtil.GetValueInt(cRow["Column2"])
        ///         sColumn3 = DBUtil.GetValueStr(cRow["Column3"], "AA")
        ///     }
        /// }
        /// </code>
        /// </example>
        public DataTable GetDataTable(string sQuery)
        {
            DataTable cDataTable = new DataTable();

            try
            {
                this.cSqlConn.Open();
                SqlDataAdapter cSDAdapter = new SqlDataAdapter(sQuery, this.cSqlConn);
                cSDAdapter.Fill(cDataTable);

                return cDataTable;
            }
            catch (Exception ex)
            {
                throw NewCustomException(ex, sQuery);
            }
            finally
            {
                this.cSqlConn.Close();
            }
        }

        /// <summary>
        /// Gets data for Datatable.
        /// </summary>
        /// <param name="sQuery">Querystring</param>
        /// <param name="sTableName">Target table name</param>
        /// <returns>DataSet</returns>
        /// <example>
        /// <code>
        /// private void Example() {
        ///     DBUtil cDBUtil = new DBUtil("server=Example;uid=test;pwd=test;database=Example");
        ///     string sSql;
        ///     DataSet cDataSet;
        /// 
        ///     sSql = "SELECT * FROM Example1";
        ///     cDataSet = cDBUtil.GetDataSet(sSql, "Example1");
        /// 
        ///     sSql = "SELECT * FROM Example2";
        ///     cDBUtil.AddDataSetTable(cDataSet, sSql, "Example2");
        /// }
        /// </code>
        /// </example>
        public DataSet GetDataSet(string sQuery, string sTableName)
        {
            DataSet cDataSet = new DataSet();

            try
            {
                this.cSqlConn.Open();
                SqlDataAdapter cSDAdapter = new SqlDataAdapter(sQuery, this.cSqlConn);
                cSDAdapter.Fill(cDataSet, sTableName);

                return cDataSet;
            }
            catch (Exception ex)
            {
                throw NewCustomException(ex, sQuery);
            }
            finally
            {
                this.cSqlConn.Close();
            }
        }

        /// <summary>
        /// Gets data for Datatable. Default table name is "DefTable".
        /// </summary>
        /// <param name="sQuery">Querystring</param>
        /// <returns>DataSet</returns>
        public DataSet GetDataSet(string sQuery)
        {
            return GetDataSet(sQuery, "DefTable");
        }

        /// <summary>
        /// Adds data table to DataSet.
        /// </summary>
        /// <param name="cDataSet">Target DataSet</param>
        /// <param name="sQuery">Querystring</param>
        /// <param name="sTableName">DataTable Name</param>
        /// <example>
        /// <code>
        /// private void Example() {
        ///     DBUtil cDBUtil = new DBUtil("server=Example;uid=test;pwd=test;database=Example");
        ///     string sSql;
        ///     DataSet cDataSet;
        /// 
        ///     sSql = "SELECT * FROM Example1";
        ///     cDataSet = cDBUtil.GetDataSet(sSql, "Example1");
        /// 
        ///     sSql = "SELECT * FROM Example2";
        ///     cDBUtil.AddDataSetTable(cDataSet, sSql, "Example2");
        /// }
        /// </code>
        /// </example>
        public void AddDataSetTable(ref DataSet cDataSet, string sQuery, string sTableName)
        {
            try
            {
                this.cSqlConn.Open();
                SqlDataAdapter cSDAdapter = new SqlDataAdapter(sQuery, this.cSqlConn);
                cSDAdapter.Fill(cDataSet, sTableName);
            }
            catch (Exception ex)
            {
                throw NewCustomException(ex, sQuery);
            }
            finally
            {
                this.cSqlConn.Close();
            }
        }

        #endregion

        #region " Public Static Methods "

        /// <summary>
        /// Gets value for String type. Default value is "".
        /// </summary>
        public static string GetValStr(DataRow dr, string sColumn)
        {
            return GetValStr(dr, sColumn, string.Empty);
        }

        /// <summary>
        /// Gets value for String type.
        /// </summary>
        public static string GetValStr(DataRow dr, string sColumn, string sDefaultValue)
        {
            if (dr.Table.Columns.Contains(sColumn))
            {
                if (dr[sColumn] == null || dr[sColumn] == System.DBNull.Value)
                    return sDefaultValue;
                else
                    return dr[sColumn].ToString().Trim();
            }
            else
            {
                return sDefaultValue;
            }
        }

        /// <summary>
        /// Gets value for String type. Default DateFormat is "yyyy-MM-dd hh:mm:ss".
        /// </summary>
        public static string GetValStrDateTime(DataRow dr, string sColumn)
        {
            return GetValStrDateTime(dr, sColumn, "yyyy-MM-dd hh:mm:ss");
        }

        /// <summary>
        /// Gets value for String type.
        /// </summary>
        public static string GetValStrDateTime(DataRow dr, string sColumn, string sDateFormat)
        {
            if (dr.Table.Columns.Contains(sColumn))
            {
                if (dr[sColumn] == null || dr[sColumn] == System.DBNull.Value)
                    return string.Empty;
                else
                    return Convert.ToDateTime(dr[sColumn]).ToString(sDateFormat);
            }
            else
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// Gets value for Integer type. Default value is 0.
        /// </summary>
        public static int GetValInt(DataRow dr, string sColumn)
        {
            return GetValInt(dr, sColumn, 0);
        }

        /// <summary>
        /// Gets value for Integer type.
        /// </summary>
        public static int GetValInt(DataRow dr, string sColumn, int iDefaultValue)
        {
            if (dr.Table.Columns.Contains(sColumn))
            {
                if (dr[sColumn] == null || dr[sColumn] == System.DBNull.Value)
                    return iDefaultValue;
                else
                    return Convert.ToInt32(dr[sColumn]);
            }
            else
            {
                return iDefaultValue;
            }
        }

        /// <summary>
        /// Gets value for Double type. Default value is 0.
        /// </summary>
        public static double GetValDouble(DataRow dr, string sColumn)
        {
            return GetValDouble(dr, sColumn, 0);
        }

        /// <summary>
        /// Gets value for Double type.
        /// </summary>
        public static double GetValDouble(DataRow dr, string sColumn, int iDefaultValue)
        {
            if (dr.Table.Columns.Contains(sColumn))
            {
                if (dr[sColumn] == null || dr[sColumn] == System.DBNull.Value)
                    return iDefaultValue;
                else
                    return Convert.ToDouble(dr[sColumn]);
            }
            else
            {
                return iDefaultValue;
            }
        }

        /// <summary>
        /// Gets value for Decimal type. Default value is 0.
        /// </summary>
        public static decimal GetValDec(DataRow dr, string sColumn)
        {
            return GetValDec(dr, sColumn, 0);
        }

        /// <summary>
        /// Gets value for Decimal type.
        /// </summary>
        public static decimal GetValDec(DataRow dr, string sColumn, int iDefaultValue)
        {
            if (dr.Table.Columns.Contains(sColumn))
            {
                if (dr[sColumn] == null || dr[sColumn] == System.DBNull.Value)
                    return iDefaultValue;
                else
                    return Convert.ToDecimal(dr[sColumn]);
            }
            else
            {
                return iDefaultValue;
            }
        }

        /// <summary>
        /// Gets value for bool type. Default value is true.
        /// </summary>
        public static bool GetValBool(DataRow dr, string sColumn)
        {
            return GetValBool(dr, sColumn, true);
        }

        /// <summary>
        /// Gets value for bool type.
        /// </summary>
        public static bool GetValBool(DataRow dr, string sColumn, bool bDefaultValue)
        {
            if (dr.Table.Columns.Contains(sColumn))
            {
                if (dr[sColumn] == null || dr[sColumn] == System.DBNull.Value)
                    return bDefaultValue;
                else
                    return Convert.ToBoolean(dr[sColumn]);
            }
            else
            {
                return bDefaultValue;
            }
        }

        /// <summary>
        /// Gets value for DateTime type. Default value is MinValue.
        /// </summary>
        public static DateTime GetValDateTime(DataRow dr, string sColumn)
        {
            return GetValDateTime(dr, sColumn, DateTime.MinValue);
        }

        /// <summary>
        /// Gets value for DateTime type.
        /// </summary>
        public static DateTime GetValDateTime(DataRow dr, string sColumn, DateTime dtDefaultValue)
        {
            if (dr.Table.Columns.Contains(sColumn))
            {
                if (dr[sColumn] == null || dr[sColumn] == System.DBNull.Value)
                    return dtDefaultValue;
                else
                    return Convert.ToDateTime(dr[sColumn]);
            }
            else
            {
                return dtDefaultValue;
            }
        }

        /// <summary>
        /// Set string to query format.
        /// </summary>
        public static string FormatCol(object Value, bool IsContinue = false)
        {
            string sResult = null;

            if (Value == null)
            {
                sResult = "''";
            }
            else
            {
                switch (Value.GetType().Name)
                {
                    case "String":
                        if (Value.ToString() == "NULL")
                            sResult = "NULL";
                        else
                            sResult = "'" + Value.ToString().Replace("'", "''") + "'";

                        break;
                    case "Int32":
                        sResult = Value.ToString();

                        break;
                    case "Decimal":
                        sResult = Value.ToString();

                        break;
                    case "DateTime":
                        if ((DateTime)Value == DateTime.MinValue)
                        {
                            sResult = "NULL";
                        }
                        else
                        {
                            sResult = "'" + ((DateTime)Value).ToString("yyyy-MM-dd HH:mm:ss") + "'";
                        }

                        break;
                    case "Boolean":
                        if ((bool)Value == true)
                            sResult = "1";
                        else
                            sResult = "0";

                        break;
                    default:
                        sResult = "'" + Value.ToString() + "'";

                        break;
                }
            }

            if (IsContinue == true)
                sResult += ", ";

            return sResult;
        }

        #endregion

    }
}
